package com.delamer.facile.db;

import com.delamer.facile.obj.ObjectPermit;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class DBUtilPermit {
    private static final String JDBC_URL = "jdbc:h2:" + System.getProperty("user.dir") + "/plugins/DelamerFacile/database/df_permit";
    private static final String USERNAME = "yn89b5";
    private static final String PASSWORD = "9iqx7f";
    private static final String DRIVER = "org.h2.Driver";
    private static Connection connection = null;
    private static Statement statement = null;
    private static final String INIT = "CREATE TABLE IF NOT EXISTS df_permit("
            + "user_name VARCHAR(50) NOT NULL,"
            + "permit_name VARCHAR(50) NOT NULL,"
            + "id INT NOT NULL PRIMARY KEY AUTO_INCREMENT);";

    public static void init() {
        if (statement == null)
            try {
                open();
                statement.execute(INIT);
            } catch (Exception e) {
                e.printStackTrace();
            }
    }

    public static void open() {
        try {
            Class.forName(DRIVER);
            connection = DriverManager.getConnection(JDBC_URL, USERNAME, PASSWORD);
            statement = connection.createStatement();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void close() {
        try {
            statement.close();
            connection.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void close(Statement statement) {
        try {
            Connection connection = statement.getConnection();
            statement.close();
            connection.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private static ResultSet select(String sql) {
        try {
            ResultSet resultSet = statement.executeQuery(sql);
            return resultSet;
        } catch (Exception e) {
            e.printStackTrace();
            close();
            return null;
        }
    }

    private static int insert(String sql) {
        try {
            int count = statement.executeUpdate(sql);
            return count;
        } catch (Exception e) {
            e.printStackTrace();
            close();
            return 0;
        }
    }

    private static int update(String sql) {
        try {
            int count = statement.executeUpdate(sql);
            return count;
        } catch (Exception e) {
            e.printStackTrace();
            close();
            return 0;
        }
    }

    private static int delete(String sql) {
        try {
            int count = statement.executeUpdate(sql);
            return count;
        } catch (Exception e) {
            e.printStackTrace();
            close();
            return 0;
        }
    }

    public static ObjectPermit getPermit(String userName, String permitName) throws SQLException {
        ResultSet rs = select("SELECT * FROM df_permit WHERE user_name = '" + userName + "' AND permit_name = '" + permitName + "';");
        List<ObjectPermit> list = new ArrayList<ObjectPermit>();
        if (rs != null)
            while (rs.next()) {
                ObjectPermit object = new ObjectPermit() {{
                    setUserName(rs.getString("user_name"));
                    setPermitName(rs.getString("permit_name"));
                }};
                list.add(object);
            }
        if (list.size() > 0)
            return list.get(0);
        return null;
    }

    public static List<ObjectPermit> getPermitList(String userName) throws SQLException {
        ResultSet rs = select("SELECT * FROM df_permit WHERE user_name = '" + userName + "';");
        List<ObjectPermit> list = new ArrayList<ObjectPermit>();
        if (rs != null)
            while (rs.next()) {
                ObjectPermit object = new ObjectPermit() {{
                    setUserName(rs.getString("user_name"));
                    setPermitName(rs.getString("permit_name"));
                }};
                list.add(object);
            }
        return list;
    }

    public static boolean addPermit(ObjectPermit object) throws SQLException {
        String sql = "INSERT INTO df_permit(user_name,permit_name) VALUES("
                + "'" + object.getUserName() + "',"
                + "'" + object.getPermitName() + "'"
                + ");";
        return insert(sql) > 0;
    }

    public static boolean delPermit(ObjectPermit object) throws SQLException {
        String sql = "DELETE FROM df_permit "
                + "WHERE user_name = '" + object.getUserName() + "' "
                + "AND permit_name = '" + object.getPermitName() + "';";
        return delete(sql) > 0;
    }
}
